/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.db.SqlManager;
import com.exception.DataException;
import com.javabeans.Departamento;
import com.javabeans.Gestion;
import com.javabeans.Facultad;
/**
 * @author Administrador
 * Estado Gestion; 0 = gestion pasada; 1 = gestion actual; 2 = gestion por venir
 */
public class AdminGestion implements GestionManager{
    private Connection c;

    public AdminGestion(Connection c){
        this.c = c;
    }
    
    @Override
    public int getIdGestion() throws DataException {
        int res = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IFNULL(max(IDGESTION)+1,1) AS ID FROM GESTION";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            rs.next();
            res = rs.getInt("ID");
        }catch(SQLException e){
            throw new DataException(" AdminGestion -> getIdGestion() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }

    @Override
    public void saveGestion(Gestion g) throws DataException {
        PreparedStatement preSt = null;
        String query = "";
        try{
            query="INSERT INTO GESTION VALUES("+g.getIdGestion();
            query+= ", '"+g.getNombre().toUpperCase();
            query+= "', "+g.getYear();
            query+= ", '"+g.getFechaIni();
            query+= "', '"+g.getFechaFin();
            query+= "', "+g.getEstado()+",0,"+g.getIdTipoGestion()+","+g.getIdFacultad()+")";
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminGestion -> saveGestion(Gestion g) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public List<Gestion> getGestVigentesYfuturos() throws DataException{
        List<Gestion> gestiones = new ArrayList<Gestion>();
        try{
            gestiones = this.getGestionesGeneric(0, false,false);
        }catch(SQLException e){
            throw new DataException(" AdminFacultad -> getGestVigentesYfuturos() e = "+e.getMessage());
        }
        return gestiones;
    }
    
    @Override
    public List<Gestion> getGestVigentesYfuturosPorFacult(int idFacultad) throws DataException{
        List<Gestion> gestiones = new ArrayList<Gestion>();
        try{
            gestiones = this.getGestionesGeneric(idFacultad, false, false);//lsmatricula by carlos
        }catch(SQLException e){
            throw new DataException(" AdminFacultad -> getGestVigentesYfuturosPorFacult(int idFacultad) e = "+e.getMessage());
        }
        return gestiones;
    }
    
    @Override
    public List<Gestion> getGestPasadas() throws DataException{
        List<Gestion> gestiones = new ArrayList<Gestion>();
        try{
            gestiones = this.getGestionesGeneric(0, true, false);
        }catch(SQLException e){
            throw new DataException(" AdminFacultad -> getGestPasadas() e = "+e.getMessage());
        }
        return gestiones;
    }
    
    @Override
    public List<Gestion> getGestPasadasPorFacul(int idFacultad) throws DataException{
        List<Gestion> gestiones = new ArrayList<Gestion>();
        try{
            gestiones = this.getGestionesGeneric(idFacultad, true, false);
        }catch(SQLException e){
            throw new DataException(" AdminFacultad -> getGestPasadasPorFacul() e = "+e.getMessage());
        }
        return gestiones;
    }    
    public List<Gestion> getGestionesGeneric(int idFacultad, boolean isGestPasados, boolean lsMatricula) throws DataException{
        List<Gestion> gestiones = new ArrayList<Gestion>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT G.IDGESTION, G.NOMBRE, G.YEAR";
            query += ", DATE_FORMAT(G.FECHAINI,'%d/%m/%Y') FECHA_INI";
            query += ", DATE_FORMAT(G.FECHAFIN,'%d/%m/%Y') FECHA_FIN";
            query += ", G.ESTADO, G.IDTIPO_GESTION";
            query += ", G.ITIGENERADO";
            query += ", F.IDFACULTAD, F.NOMBRE, F.SIGLA";
            query += ", D.NOMBRE, D.SIGLA";
            query += ", TG.NOMBRE";
            query += " FROM GESTION G";
            query += " INNER JOIN (FACULTAD F INNER JOIN DEPARTAMENTO D ON F.IDDEPARTAMENTO=D.IDDEPARTAMENTO)";
            query += " ON G.IDFACULTAD=F.IDFACULTAD";
            if(idFacultad != 0)
                query += " AND G.IDFACULTAD="+idFacultad;
            if(lsMatricula)
                query += " INNER JOIN VENTA_MATRICULA VM ON G.IDGESTION=VM.IDGESTION AND VM.ACTIVO=1";//car
            query += " INNER JOIN TIPO_GESTION TG ON G.IDTIPO_GESTION=.TG.IDTIPO_GESTION AND TG.ACTIVO=1";
            query += " WHERE";
            if(isGestPasados)
                query += " G.ESTADO = 0";
            else
                query += " G.ESTADO > 0";
            // para ordenar
            if(idFacultad != 0)
                query += " ORDER BY G.ESTADO, G.FECHAINI";
            else
                query += " ORDER BY F.NOMBRE, G.ESTADO, G.FECHAINI";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Gestion g = new Gestion();
                g.setIdGestion(rs.getInt("G.IDGESTION"));
                g.setNombre(rs.getString("G.NOMBRE"));
                g.setYear(rs.getInt("G.YEAR"));
                g.setFechaIni(rs.getString("FECHA_INI"));
                g.setFechaFin(rs.getString("FECHA_FIN"));
                g.setEstado(rs.getInt("G.ESTADO"));
                g.setIdTipoGestion(rs.getInt("G.IDTIPO_GESTION"));
                g.setItiGenerado(rs.getBoolean("G.ITIGENERADO"));
                g.setIdFacultad(rs.getInt("F.IDFACULTAD"));
                
                Facultad f = new Facultad();
                f.setIdFacultad(rs.getInt("F.IDFACULTAD"));
                f.setNombre(rs.getString("F.NOMBRE"));
                f.setSigla(rs.getString("F.SIGLA"));
                Departamento d = new Departamento();
                d.setNombre(rs.getString("D.NOMBRE"));
                d.setSigla(rs.getString("D.SIGLA"));
                f.setDepartamento(d);
                g.setFacultad(f);
                gestiones.add(g);
            }
        }catch(SQLException e){
            throw new DataException(" -> getGestionesGeneric(int idFacultad, int estado, boolean isGestPasados) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return gestiones;
    }
    
    @Override
    public List<Gestion> getGestVigentesYfuturosBasico() throws DataException{
        List<Gestion> gestiones = new ArrayList<Gestion>();
        try{
            gestiones = this.getGestionesGenericBasico(0, false);
        }catch(SQLException e){
            throw new DataException(" AdminFacultad -> getGestVigentesYfuturosBasico() e = "+e.getMessage());
        }
        return gestiones;
    }
    
    @Override
    public List<Gestion> getGestVigentesYfuturosPorFacultBasico(int idFacultad) throws DataException{
        List<Gestion> gestiones = new ArrayList<Gestion>();
        try{
            gestiones = this.getGestionesGenericBasico(idFacultad, false);
        }catch(SQLException e){
            throw new DataException(" AdminFacultad -> getGestVigentesYfuturosPorFacultBasico(int idFacultad) e = "+e.getMessage());
        }
        return gestiones;
    }
    
    @Override
    public List<Gestion> getGestPasadasBasico() throws DataException{
        List<Gestion> gestiones = new ArrayList<Gestion>();
        try{
            gestiones = this.getGestionesGenericBasico(0, true);
        }catch(SQLException e){
            throw new DataException(" AdminFacultad -> getGestPasadasBasico() e = "+e.getMessage());
        }
        return gestiones;
    }
    
    @Override
    public List<Gestion> getGestPasadasPorFaculBasico(int idFacultad) throws DataException{
        List<Gestion> gestiones = new ArrayList<Gestion>();
        try{
            gestiones = this.getGestionesGenericBasico(idFacultad, true);
        }catch(SQLException e){
            throw new DataException(" AdminFacultad -> getGestPasadasPorFaculBasico() e = "+e.getMessage());
        }
        return gestiones;
    }
    
    public List<Gestion> getGestionesGenericBasico(int idFacultad, boolean isGestPasados) throws DataException{
        List<Gestion> gestiones = new ArrayList<Gestion>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDGESTION, NOMBRE, YEAR";
            query += ", DATE_FORMAT(FECHAINI,'%d/%m/%Y') FECHA_INI";
            query += ", DATE_FORMAT(FECHAFIN,'%d/%m/%Y') FECHA_FIN";
            query += ", ESTADO, IDTIPO_GESTION";
            query += ", ITIGENERADO";
            query += ", IDFACULTAD";
            query += " FROM GESTION";
            query += " WHERE";
            if(idFacultad != 0){
                query += " IDFACULTAD="+idFacultad;
                if(isGestPasados)
                    query += " AND ESTADO = 0";
                else
                    query += " AND ESTADO > 0";
            }else{
                if(isGestPasados)
                    query += " ESTADO = 0";
                else
                    query += " ESTADO > 0";
            }
            // para ordenar
            if(idFacultad != 0)
                query += " ORDER BY ESTADO, FECHAINI";
            else
                query += " ORDER BY IDFACULTAD, ESTADO, FECHAINI";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Gestion g = new Gestion();
                g.setIdGestion(rs.getInt("IDGESTION"));
                g.setNombre(rs.getString("NOMBRE"));
                g.setYear(rs.getInt("YEAR"));
                g.setFechaIni(rs.getString("FECHA_INI"));
                g.setFechaFin(rs.getString("FECHA_FIN"));
                g.setEstado(rs.getInt("ESTADO"));
                g.setIdTipoGestion(rs.getInt("IDTIPO_GESTION"));
                g.setItiGenerado(rs.getBoolean("ITIGENERADO"));
                g.setIdFacultad(rs.getInt("IDFACULTAD"));
                gestiones.add(g);
            }
        }catch(SQLException e){
            throw new DataException(" -> getGestionesGenericBasico(int idFacultad, int estado, boolean isGestPasados) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return gestiones;
    }
    
    public List<Gestion> getGestionesByFacultYear(int idFacultad, int year) throws DataException{
        List<Gestion> gestiones = new ArrayList<Gestion>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT G.IDGESTION, G.NOMBRE, G.YEAR";
            query += ", DATE_FORMAT(G.FECHAINI,'%d/%m/%Y') FECHA_INI";
            query += ", DATE_FORMAT(G.FECHAFIN,'%d/%m/%Y') FECHA_FIN";
            query += ", G.ESTADO, G.ITIGENERADO, G.IDTIPO_GESTION";
            query += ", F.IDFACULTAD, F.NOMBRE, F.SIGLA";
            query += ", D.NOMBRE, D.SIGLA";
            query += ", TG.NOMBRE";
            query += " FROM GESTION G";
            query += " INNER JOIN (FACULTAD F INNER JOIN DEPARTAMENTO D ON F.IDDEPARTAMENTO=D.IDDEPARTAMENTO)";
            query += " ON G.IDFACULTAD=F.IDFACULTAD";
            query += " AND G.IDFACULTAD="+idFacultad;
            query += " INNER JOIN TIPO_GESTION TG ON G.IDTIPO_GESTION=.TG.IDTIPO_GESTION";
            query += " WHERE G.YEAR >= "+year;
            // para ordenar
            query += " ORDER BY F.NOMBRE, G.ESTADO, G.FECHAINI";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Gestion g = new Gestion();
                g.setIdGestion(rs.getInt("G.IDGESTION"));
                g.setNombre(rs.getString("G.NOMBRE"));
                g.setYear(rs.getInt("G.YEAR"));
                g.setFechaIni(rs.getString("FECHA_INI"));
                g.setFechaFin(rs.getString("FECHA_FIN"));
                g.setEstado(rs.getInt("G.ESTADO"));
                g.setItiGenerado(rs.getBoolean("G.ITIGENERADO"));
                g.setIdTipoGestion(rs.getInt("G.IDTIPO_GESTION"));
                g.setIdFacultad(rs.getInt("F.IDFACULTAD"));
                
                Facultad f = new Facultad();
                f.setIdFacultad(rs.getInt("F.IDFACULTAD"));
                f.setNombre(rs.getString("F.NOMBRE"));
                f.setSigla(rs.getString("F.SIGLA"));
                Departamento d = new Departamento();
                d.setNombre(rs.getString("D.NOMBRE"));
                d.setSigla(rs.getString("D.SIGLA"));
                f.setDepartamento(d);
                g.setFacultad(f);
                gestiones.add(g);
            }
        }catch(SQLException e){
            throw new DataException(" -> getGestionesGeneric(int idFacultad, int estado, boolean isGestPasados) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return gestiones;
    }
    
    public List<Gestion> getGestionesBasicByYear(int year) throws DataException{
        List<Gestion> gestiones = new ArrayList<Gestion>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT G.IDGESTION, G.NOMBRE, G.YEAR";
            query += ", DATE_FORMAT(G.FECHAINI,'%d/%m/%Y') FECHA_INI";
            query += ", DATE_FORMAT(G.FECHAFIN,'%d/%m/%Y') FECHA_FIN";
            query += ", G.ESTADO, G.IDFACULTAD";
            query += " FROM GESTION G";
            query += " WHERE G.YEAR >= "+year;
            query += " ORDER BY G.FECHAINI";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Gestion g = new Gestion();
                g.setIdGestion(rs.getInt("G.IDGESTION"));
                g.setNombre(rs.getString("G.NOMBRE"));
                g.setYear(rs.getInt("G.YEAR"));
                g.setFechaIni(rs.getString("FECHA_INI"));
                g.setFechaFin(rs.getString("FECHA_FIN"));
                g.setEstado(rs.getInt("G.ESTADO"));
                g.setIdFacultad(rs.getInt("G.IDFACULTAD"));
                gestiones.add(g);
            }
        }catch(SQLException e){
            throw new DataException(" AdminGestion -> getGestionesBasicByFacultYear(int idFacultad, int year) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return gestiones;
    }
    
    public Gestion getGestion(int idGestion) throws DataException{
        Gestion g = new Gestion();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT G.IDGESTION, G.NOMBRE, G.YEAR";
            query += ", DATE_FORMAT(G.FECHAINI,'%d/%m/%Y') FECHA_INI";
            query += ", DATE_FORMAT(G.FECHAFIN,'%d/%m/%Y') FECHA_FIN";
            query += ", G.ESTADO, G.IDTIPO_GESTION";
            query += ", F.IDFACULTAD, F.NOMBRE, F.SIGLA";
            query += ", D.NOMBRE, D.SIGLA";
            query += ", TG.NOMBRE";
            query += " FROM GESTION G";
            query += " INNER JOIN (FACULTAD F INNER JOIN DEPARTAMENTO D ON F.IDDEPARTAMENTO=D.IDDEPARTAMENTO)";
            query += " ON G.IDFACULTAD=F.IDFACULTAD";
            query += " INNER JOIN TIPO_GESTION TG ON G.IDTIPO_GESTION=.TG.IDTIPO_GESTION";
            query += " WHERE G.IDGESTION="+idGestion;            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next()){
                g.setIdGestion(rs.getInt("G.IDGESTION"));
                g.setNombre(rs.getString("G.NOMBRE"));
                g.setYear(rs.getInt("G.YEAR"));
                g.setFechaIni(rs.getString("FECHA_INI"));
                g.setFechaFin(rs.getString("FECHA_FIN"));
                g.setEstado(rs.getInt("G.ESTADO"));
                g.setIdTipoGestion(rs.getInt("G.IDTIPO_GESTION"));
                g.setIdFacultad(rs.getInt("F.IDFACULTAD"));
                
                Facultad f = new Facultad();
                f.setIdFacultad(rs.getInt("F.IDFACULTAD"));
                f.setNombre(rs.getString("F.NOMBRE"));
                f.setSigla(rs.getString("F.SIGLA"));
                Departamento d = new Departamento();
                d.setNombre(rs.getString("D.NOMBRE"));
                d.setSigla(rs.getString("D.SIGLA"));
                f.setDepartamento(d);
                g.setFacultad(f);
            }
        }catch(SQLException e){
            throw new DataException(" -> getGestion(int idGestion) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return g;
    }
    
    public Gestion getGestionBasic(int idGestion) throws DataException{
        Gestion g = new Gestion();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT NOMBRE";
            query += ", YEAR";
            query += ", DATE_FORMAT(FECHAINI,'%d/%m/%Y') FECHA_INI";
            query += ", DATE_FORMAT(FECHAFIN,'%d/%m/%Y') FECHA_FIN";
            query += ", ESTADO";
            query += ", IDFACULTAD";
            query += ", IDTIPO_GESTION";
            query += " FROM GESTION";
            query += " WHERE IDGESTION="+idGestion;            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next()){
                g.setIdGestion(idGestion);
                g.setNombre(rs.getString("NOMBRE"));
                g.setYear(rs.getInt("YEAR"));
                g.setFechaIni(rs.getString("FECHA_INI"));
                g.setFechaFin(rs.getString("FECHA_FIN"));
                g.setEstado(rs.getInt("ESTADO"));
                g.setIdFacultad(rs.getInt("IDFACULTAD"));
                g.setIdTipoGestion(rs.getInt("IDTIPO_GESTION"));
            }
        }catch(SQLException e){
            throw new DataException(" -> getGestionBasic(int idGestion) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return g;
    }
    
    @Override
    public void updateGestion(Gestion g) throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE GESTION SET NOMBRE = '"+g.getNombre().toUpperCase();
            query+= "', YEAR = "+g.getYear();
            query+= ", FECHAINI = '"+g.getFechaIni();
            query+= "', FECHAFIN = '"+g.getFechaFin();
            query+= "', IDTIPO_GESTION = "+g.getIdTipoGestion();
            query+= " WHERE IDGESTION="+g.getIdGestion();            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminGestion -> updateGestion(Gestion g) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public boolean isUseInMatricula(int idGestion) throws DataException{
        boolean res = false;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IDGESTION";
        query += " FROM MATRICULA";
        query += " WHERE IDGESTION="+idGestion+" LIMIT 1";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next())
                res = true;
        }catch(SQLException e){
            throw new DataException(" AdminGestion -> isUseInMatricula() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }
    
    public boolean isUseInInscrito(int idGestion) throws DataException{
        boolean res = false;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IDINSCRITO";
        query += " FROM INSCRITO";
        query += " WHERE IDGESTION="+idGestion+" LIMIT 1";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next())
                res = true;
        }catch(SQLException e){
            throw new DataException(" AdminGestion -> isUseInInscrito() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }
    
    @Override
    public void deleteGestion(int idGestion) throws DataException {
        PreparedStatement preSt = null;
        String query = "";
        try{
            query = "DELETE FROM GRUPO_MATERIA WHERE IDGESTION="+idGestion;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();

            query = "DELETE FROM GRUPO WHERE IDGESTION="+idGestion;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            
            query = "DELETE FROM VENTA_MATRICULA WHERE IDGESTION="+idGestion;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            
            query = "DELETE FROM GESTION WHERE IDGESTION="+idGestion;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException(" AdminGestion -> deleteGestion(int idGestion) query ====== "+query+" e = "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public void cerrarGestion(int idGestion, int idFacultad) throws DataException{
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE GESTION SET ESTADO=0"; 
            query += " WHERE IDGESTION="+idGestion;            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            query = "SELECT IDGESTION FROM GESTION WHERE IDFACULTAD="+idFacultad+" AND ESTADO=2  ORDER BY FECHAINI";
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next()){
                query = "UPDATE GESTION SET ESTADO=1"; 
                query += " WHERE IDGESTION="+rs.getInt("IDGESTION");
                preSt = c.prepareStatement(query);
                preSt.executeUpdate();
            }
        }catch(SQLException ex){
            throw new DataException(" AdminGestion -> cerrarGestion(int idGestion, int idFacultad) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public void updateItiGenerado(int idGestion) throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE GESTION SET ITIGENERADO=1"; 
            query += " WHERE IDGESTION="+idGestion;            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminGestion -> updateItiGenerado(int idGestion) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
}